package com.bw.dao;

import com.bw.bean.Sugar;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select p.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN base_province p ON o.province_id=p.id\n" +
            "GROUP BY p.`name`")
    List<Sugar> getList1();

    @Select("select b.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id \n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id\n" +
            "LEFT JOIN base_trademark b ON s.tm_id=b.id \n" +
            "where b.name is not null\n" +
            "GROUP BY b.`name`   ")
    List<Sugar> getList2();

    @Select("select c1.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id\n" +
            "LEFT JOIN base_category3 c3 ON c3.id=s.category3_id\n" +
            "LEFT JOIN base_category2 c2 ON c2.id=c3.category2_id\n" +
            "LEFT JOIN base_category1 c1 ON c1.id=c2.category1_id\n" +
            "where c1.`name` is not NULL\n" +
            "GROUP BY c1.`name`")
    List<Sugar> getList3();

    @Select("select * from \n" +
            "(select count(*) 'name' from user_info where TIMESTAMPDIFF(month,create_time,NOW())<1)a,\n" +
            "(select count(*) 'value' from user_info where TIMESTAMPDIFF(month,create_time,NOW())>1)b")
    List<Sugar> getList4();
}
